import { BaseModel } from './BaseModel';
import { query } from '../../config/database';

export interface IExecution {
  id: string;
  script_id: string;
  connection_id: string;
  status: 'running' | 'completed' | 'failed' | 'cancelled';
  start_time: Date;
  end_time?: Date;
  duration?: number; // 毫秒
  rows_affected?: number;
  error_message?: string;
  execution_plan?: string;
  result_preview?: any; // JSON数据
  created_at: Date;
  executed_by?: string;
}

export interface ExecutionStats {
  total_executions: number;
  successful_executions: number;
  failed_executions: number;
  cancelled_executions: number;
  avg_duration: number;
  total_rows_affected: number;
}

export class Execution extends BaseModel {
  constructor() {
    super('executions');
  }
  
  // 创建新的执行记录
  async createExecution(executionData: {
    script_id: string;
    connection_id: string;
    executed_by?: string;
  }): Promise<IExecution> {
    const result = await query(
      `INSERT INTO executions (script_id, connection_id, status, start_time, executed_by)
       VALUES ($1, $2, 'running', CURRENT_TIMESTAMP, $3)
       RETURNING *`,
      [executionData.script_id, executionData.connection_id, executionData.executed_by]
    );
    return result.rows[0];
  }
  
  // 更新执行状态为完成
  async completeExecution(id: string, data: {
    rows_affected?: number;
    execution_plan?: string;
    result_preview?: any;
  }): Promise<IExecution | null> {
    const result = await query(
      `UPDATE executions 
       SET status = 'completed', 
           end_time = CURRENT_TIMESTAMP,
           duration = EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - start_time)) * 1000,
           rows_affected = $2,
           execution_plan = $3,
           result_preview = $4
       WHERE id = $1
       RETURNING *`,
      [id, data.rows_affected, data.execution_plan, JSON.stringify(data.result_preview)]
    );
    return result.rows[0] || null;
  }
  
  // 更新执行状态为失败
  async failExecution(id: string, errorMessage: string): Promise<IExecution | null> {
    const result = await query(
      `UPDATE executions 
       SET status = 'failed', 
           end_time = CURRENT_TIMESTAMP,
           duration = EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - start_time)) * 1000,
           error_message = $2
       WHERE id = $1
       RETURNING *`,
      [id, errorMessage]
    );
    return result.rows[0] || null;
  }
  
  // 取消执行
  async cancelExecution(id: string): Promise<IExecution | null> {
    const result = await query(
      `UPDATE executions 
       SET status = 'cancelled', 
           end_time = CURRENT_TIMESTAMP,
           duration = EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - start_time)) * 1000
       WHERE id = $1 AND status = 'running'
       RETURNING *`,
      [id]
    );
    return result.rows[0] || null;
  }
  
  // 获取脚本的执行历史
  async getExecutionHistory(scriptId: string, options: {
    limit?: number;
    offset?: number;
    status?: string;
    connection_id?: string;
    executed_by?: string;
  } = {}): Promise<{ executions: IExecution[]; total: number }> {
    let sql = `SELECT e.*, s.name as script_name, c.name as connection_name
               FROM executions e
               LEFT JOIN scripts s ON e.script_id = s.id
               LEFT JOIN connections c ON e.connection_id = c.id
               WHERE e.script_id = $1`;
    let countSql = 'SELECT COUNT(*) as total FROM executions WHERE script_id = $1';
    const params: any[] = [scriptId];
    let paramIndex = 2;
    
    // 状态过滤
    if (options.status) {
      const statusCondition = ` AND e.status = $${paramIndex}`;
      sql += statusCondition;
      countSql += statusCondition.replace('e.', '');
      params.push(options.status);
      paramIndex++;
    }
    
    // 连接过滤
    if (options.connection_id) {
      const connectionCondition = ` AND e.connection_id = $${paramIndex}`;
      sql += connectionCondition;
      countSql += connectionCondition.replace('e.', '');
      params.push(options.connection_id);
      paramIndex++;
    }
    
    // 执行者过滤
    if (options.executed_by) {
      const executorCondition = ` AND e.executed_by = $${paramIndex}`;
      sql += executorCondition;
      countSql += executorCondition.replace('e.', '');
      params.push(options.executed_by);
      paramIndex++;
    }
    
    // 排序
    sql += ' ORDER BY e.start_time DESC';
    
    // 分页
    if (options.limit) {
      sql += ` LIMIT $${paramIndex++}`;
      params.push(options.limit);
    }
    
    if (options.offset) {
      sql += ` OFFSET $${paramIndex++}`;
      params.push(options.offset);
    }
    
    // 执行查询
    const [executionsResult, countResult] = await Promise.all([
      query(sql, params),
      query(countSql, params.slice(0, paramIndex - (options.limit ? 1 : 0) - (options.offset ? 1 : 0)))
    ]);
    
    return {
      executions: executionsResult.rows,
      total: parseInt(countResult.rows[0].total, 10)
    };
  }
  
  // 获取连接的执行历史
  async getExecutionsByConnection(connectionId: string, options: {
    limit?: number;
    offset?: number;
    status?: string;
  } = {}): Promise<{ executions: IExecution[]; total: number }> {
    let sql = `SELECT e.*, s.name as script_name
               FROM executions e
               LEFT JOIN scripts s ON e.script_id = s.id
               WHERE e.connection_id = $1`;
    let countSql = 'SELECT COUNT(*) as total FROM executions WHERE connection_id = $1';
    const params: any[] = [connectionId];
    let paramIndex = 2;
    
    if (options.status) {
      const statusCondition = ` AND e.status = $${paramIndex}`;
      sql += statusCondition;
      countSql += statusCondition.replace('e.', '');
      params.push(options.status);
      paramIndex++;
    }
    
    sql += ' ORDER BY e.start_time DESC';
    
    if (options.limit) {
      sql += ` LIMIT $${paramIndex++}`;
      params.push(options.limit);
    }
    
    if (options.offset) {
      sql += ` OFFSET $${paramIndex++}`;
      params.push(options.offset);
    }
    
    const [executionsResult, countResult] = await Promise.all([
      query(sql, params),
      query(countSql, params.slice(0, paramIndex - (options.limit ? 1 : 0) - (options.offset ? 1 : 0)))
    ]);
    
    return {
      executions: executionsResult.rows,
      total: parseInt(countResult.rows[0].total, 10)
    };
  }
  
  // 获取正在运行的执行
  async getRunningExecutions(): Promise<IExecution[]> {
    const result = await query(
      `SELECT e.*, s.name as script_name, c.name as connection_name
       FROM executions e
       LEFT JOIN scripts s ON e.script_id = s.id
       LEFT JOIN connections c ON e.connection_id = c.id
       WHERE e.status = 'running'
       ORDER BY e.start_time ASC`
    );
    return result.rows;
  }
  
  // 获取执行统计信息
  async getExecutionStats(options: {
    script_id?: string;
    connection_id?: string;
    executed_by?: string;
    start_date?: Date;
    end_date?: Date;
  } = {}): Promise<ExecutionStats> {
    let sql = `SELECT 
                 COUNT(*) as total_executions,
                 COUNT(CASE WHEN status = 'completed' THEN 1 END) as successful_executions,
                 COUNT(CASE WHEN status = 'failed' THEN 1 END) as failed_executions,
                 COUNT(CASE WHEN status = 'cancelled' THEN 1 END) as cancelled_executions,
                 AVG(duration) as avg_duration,
                 SUM(COALESCE(rows_affected, 0)) as total_rows_affected
               FROM executions WHERE 1=1`;
    const params: any[] = [];
    let paramIndex = 1;
    
    if (options.script_id) {
      sql += ` AND script_id = $${paramIndex++}`;
      params.push(options.script_id);
    }
    
    if (options.connection_id) {
      sql += ` AND connection_id = $${paramIndex++}`;
      params.push(options.connection_id);
    }
    
    if (options.executed_by) {
      sql += ` AND executed_by = $${paramIndex++}`;
      params.push(options.executed_by);
    }
    
    if (options.start_date) {
      sql += ` AND start_time >= $${paramIndex++}`;
      params.push(options.start_date);
    }
    
    if (options.end_date) {
      sql += ` AND start_time <= $${paramIndex++}`;
      params.push(options.end_date);
    }
    
    const result = await query(sql, params);
    const stats = result.rows[0];
    
    return {
      total_executions: parseInt(stats.total_executions, 10),
      successful_executions: parseInt(stats.successful_executions, 10),
      failed_executions: parseInt(stats.failed_executions, 10),
      cancelled_executions: parseInt(stats.cancelled_executions, 10),
      avg_duration: parseFloat(stats.avg_duration) || 0,
      total_rows_affected: parseInt(stats.total_rows_affected, 10) || 0
    };
  }
  
  // 获取执行性能趋势
  async getExecutionTrends(options: {
    script_id?: string;
    connection_id?: string;
    days?: number;
  } = {}): Promise<Array<{
    date: string;
    total_executions: number;
    successful_executions: number;
    failed_executions: number;
    avg_duration: number;
  }>> {
    const days = options.days || 30;
    let sql = `SELECT 
                 DATE(start_time) as date,
                 COUNT(*) as total_executions,
                 COUNT(CASE WHEN status = 'completed' THEN 1 END) as successful_executions,
                 COUNT(CASE WHEN status = 'failed' THEN 1 END) as failed_executions,
                 AVG(duration) as avg_duration
               FROM executions 
               WHERE start_time >= CURRENT_DATE - INTERVAL '${days} days'`;
    const params: any[] = [];
    let paramIndex = 1;
    
    if (options.script_id) {
      sql += ` AND script_id = $${paramIndex++}`;
      params.push(options.script_id);
    }
    
    if (options.connection_id) {
      sql += ` AND connection_id = $${paramIndex++}`;
      params.push(options.connection_id);
    }
    
    sql += ' GROUP BY DATE(start_time) ORDER BY date DESC';
    
    const result = await query(sql, params);
    return result.rows.map((row: any) => ({
      date: row.date,
      total_executions: parseInt(row.total_executions, 10),
      successful_executions: parseInt(row.successful_executions, 10),
      failed_executions: parseInt(row.failed_executions, 10),
      avg_duration: parseFloat(row.avg_duration) || 0
    }));
  }
  
  // 清理旧的执行记录
  async cleanupOldExecutions(daysToKeep: number = 90): Promise<number> {
    const result = await query(
      `DELETE FROM executions 
       WHERE start_time < CURRENT_DATE - INTERVAL '${daysToKeep} days'
       AND status IN ('completed', 'failed', 'cancelled')`,
      []
    );
    return result.rowCount;
  }
  
  // 获取最近的执行记录
  async getRecentExecutions(limit: number = 10): Promise<IExecution[]> {
    const result = await query(
      `SELECT e.*, s.name as script_name, c.name as connection_name
       FROM executions e
       LEFT JOIN scripts s ON e.script_id = s.id
       LEFT JOIN connections c ON e.connection_id = c.id
       ORDER BY e.start_time DESC
       LIMIT $1`,
      [limit]
    );
    return result.rows;
  }
}